﻿/******************************************************   
创建者: 梁家健
创建时间: 2017-05-15
修改:
修改时间: 
功能: 城市品类码比
 ******************************************************/

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using com.nm.ApiServer;
using com.nm.ApiServer.Attributes;
using com.nm.Attributes;
using com.nm.library;
using com.nm.library.Helper;
using DistributeAPI.Model;
using george.Data;
using NHibernateVertica;
using Nancy.Extensions;

namespace DistributeAPI
{
    [Description("城市品类码比")]
    public class DCBasCitySizeRateHandler : BaseHandler
    {
        public VerticaDataHelper Helper { get; set; } = new VerticaDataHelper();

        /// Writer：梁家健
        /// Create Date：2017-05-15
        [MustLogin]
        [Description("城市品类码比列表")]
        [ReturnType(typeof(PaperData<IGrouping<string, DCBasCitySizeRateModel.ListModel>>))]
        public ApiResult GetDCBasCitySizeRateList([ParamDescription("省份")] string ProID = "",
            [ParamDescription("城市")] string CityID = "", [ParamDescription("品类")] string ModelID = "",
            [ParamDescription("页码")] int Page = 1, [ParamDescription("页数")] int Size = 10)
        {
            try
            {
                if (string.IsNullOrEmpty(UserInfo.UserCompanyId))
                {
                    return Error("该用户不属于任何公司!");
                }
                if (string.IsNullOrEmpty(UserInfo.UserExtrSource))
                {
                    return Error("该用户所在公司没有任何数据源!");
                }
                var count = ReturnDCBasCitySizeRateCount(ProID, CityID, ModelID);
                var result = PaperData<IGrouping<string, DCBasCitySizeRateModel.ListModel>>.Create(count, Size, Page);
                var dt = ReturnDCBasCitySizeRateList(ProID, CityID, ModelID, Page, Size);
                var list = ModelConvertHelper<DCBasCitySizeRateModel.ListModel>.ConvertToModel(dt);
                result.Items = list.GroupBy(d => d.CompanyID + "_" + d.AreaID + "_" + d.ModelID + "_" + d.SizeTypeFlag).ToList();
                return Success(result);
            }
            catch (Exception ex)
            {
                return Error(ex.Message);
            }
        }

        /// <summary>
        /// 返回城市品类码比列表
        /// </summary>
        /// <param name="ProID"></param>
        /// <param name="CityID"></param>
        /// <param name="KindID"></param>
        /// <param name="Page"></param>
        /// <param name="Size"></param>
        /// <returns></returns>
        /// Writer：梁家健
        /// Create Date：2017-05-15
        public DataTable ReturnDCBasCitySizeRateList(string ProID, string CityID, string ModelID, int Page, int Size)
        {
            try
            {
                var sql = $@" SELECT  table2.* ,
        rate.SizeID ,
        ISNULL(rate.SizeRate, 0) * 100 AS SizeRate ,
        si.SizeName
FROM    ( SELECT    *
          FROM      ( SELECT    rate.CompanyID ,
                                rate.ExtrSource ,
                                rate.AreaID ,
                                rate.AreaName ,
                                rate.ModelID ,
                                rate.SizeTypeFlag ,
                                model.ModelName ,
                                area.AreaCode ,
                                area.ParentID ,
                                ROW_NUMBER() OVER ( ORDER BY rate.CompanyID, rate.AreaID, rate.AreaName, rate.ModelID, model.ModelName , area.AreaCode , area.ParentID , rate.SizeTypeFlag ) AS Row
                      FROM      ngoods.ngoods_DC_Bas_CitySizeRate rate
                                INNER JOIN ( SELECT *
                                             FROM   nods.nods_erp_Bas_Area
                                             WHERE  ExtrSource = '{UserInfo.UserExtrSource}'
                                           ) area ON rate.AreaID = area.AreaID
                                                     AND rate.ExtrSource = area.ExtrSource
                                INNER JOIN ( SELECT *
                                             FROM   nods.nods_erp_SD_Mat_Model
                                             WHERE  ExtrSource = '{UserInfo.UserExtrSource}'
                                           ) model ON rate.ModelID = model.ModelID
                                                      AND rate.ExtrSource = model.ExtrSource
                      WHERE     rate.ExtrSource = '{UserInfo.UserExtrSource}'
                                AND rate.CompanyID = '{UserInfo.UserCompanyId}'
								{(string.IsNullOrEmpty(ProID) ? "" : $@" and area.ParentID='{ProID}' ")}
								{(string.IsNullOrEmpty(CityID) ? "" : $@" and rate.AreaID='{CityID}' ")}
								{(string.IsNullOrEmpty(ModelID) ? "" : $@" and rate.ModelID='{ModelID}' ")}
                      GROUP BY  rate.CompanyID ,
                                rate.ExtrSource ,
                                rate.AreaID ,
                                rate.AreaName ,
                                rate.ModelID ,
                                rate.SizeTypeFlag ,
                                model.ModelName ,
                                area.AreaCode ,
                                area.ParentID
                    ) table1
          WHERE     row BETWEEN ( {Page} - 1 ) * {Size} + 1 AND {Page}
                    * {Size}
        ) table2
        INNER JOIN ( SELECT rate.*
                     FROM   ngoods.ngoods_DC_Bas_CitySizeRate rate
                            INNER JOIN ( SELECT *
                                         FROM   nods.nods_erp_Bas_Area
                                         WHERE  ExtrSource = '{UserInfo.UserExtrSource}'
                                       ) area ON rate.AreaID = area.AreaID
                                                 AND rate.ExtrSource = area.ExtrSource
                     WHERE  rate.ExtrSource = '{UserInfo.UserExtrSource}'
                            AND rate.CompanyID = '{UserInfo.UserCompanyId}'
							{(string.IsNullOrEmpty(ProID) ? "" : $@" and area.ParentID='{ProID}' ")}
							{(string.IsNullOrEmpty(CityID) ? "" : $@" and rate.AreaID='{CityID}' ")}
							{(string.IsNullOrEmpty(ModelID) ? "" : $@" and rate.ModelID='{ModelID}' ")}
                     
                   ) rate ON table2.CompanyID = rate.CompanyID
                             AND table2.ExtrSource = rate.ExtrSource
                             AND table2.AreaID = rate.AreaID
                             AND table2.ModelID = rate.ModelID
                             AND table2.SizeTypeFlag = rate.SizeTypeFlag
        INNER JOIN ( SELECT *
                     FROM   nods.nods_erp_SD_Mat_Size
                     WHERE  ExtrSource = '{UserInfo.UserExtrSource}'
                   ) si ON rate.SizeID = si.SizeID
                           AND table2.SizeTypeFlag = si.SizeCodeType
                           AND table2.ExtrSource = si.ExtrSource
WHERE   rate.SizeRate > 0
        AND table2.ExtrSource = '{UserInfo.UserExtrSource}'
        AND table2.CompanyID = '{UserInfo.UserCompanyId}'
ORDER BY table2.CompanyID ,
        table2.ExtrSource ,
        table2.AreaID ,
        table2.AreaName ,
        table2.ModelID ,
        table2.SizeTypeFlag ,
        si.SizeCodeSort ";
                var cmd = Helper.GetSqlStringCommand(sql);
                return Helper.ExecuteDataTable(cmd);
            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }

        /// <summary>
        /// 返回城市品类码比总条数
        /// </summary>
        /// <param name="ProID"></param>
        /// <param name="CityID"></param>
        /// <param name="KindID"></param>
        /// <returns></returns>
        /// Writer：梁家健
        /// Create Date：2017-05-15
        public int ReturnDCBasCitySizeRateCount(string ProID, string CityID, string ModelID)
        {
            try
            {
                var sql = $@"SELECT    count(1)
          FROM      ( SELECT    count(1)
                      FROM      ngoods.ngoods_DC_Bas_CitySizeRate rate
                                INNER JOIN ( SELECT *
                                             FROM   nods.nods_erp_Bas_Area
                                             WHERE  ExtrSource = '{UserInfo.UserExtrSource}'
                                           ) area ON rate.AreaID = area.AreaID
                                                     AND rate.ExtrSource = area.ExtrSource
                                INNER JOIN ( SELECT *
                                             FROM   nods.nods_erp_SD_Mat_Model
                                             WHERE  ExtrSource = '{UserInfo.UserExtrSource}'
                                           ) model ON rate.ModelID = model.ModelID
                                                      AND rate.ExtrSource = model.ExtrSource
                      WHERE     rate.ExtrSource = '{UserInfo.UserExtrSource}'
                                AND rate.CompanyID = '{UserInfo.UserCompanyId}'
								{(string.IsNullOrEmpty(ProID) ? "" : $@" and area.ParentID='{ProID}' ")}
								{(string.IsNullOrEmpty(CityID) ? "" : $@" and rate.AreaID='{CityID}' ")}
								{(string.IsNullOrEmpty(ModelID) ? "" : $@" and rate.ModelID='{ModelID}' ")}
                      GROUP BY  rate.CompanyID ,
                                rate.ExtrSource ,
                                rate.AreaID ,
                                rate.AreaName ,
                                rate.ModelID ,
                                rate.SizeTypeFlag ,
                                model.ModelName ,
                                area.AreaCode ,
                                area.ParentID
                    ) table1";
                var cmd = Helper.GetSqlStringCommand(sql);
                return Convert.ToInt32(Helper.ExecuteScalar(cmd));
            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }

        /// Writer：梁家健
        /// Create Date：2017-06-06
        [JsonType]
        [MustLogin]
        [Description("新增城市品类码比")]
        [ReturnType(typeof(int), "0-成功")]
        public ApiResult UpdateDCBasCitySizeRate([ParamDescription("参数")] DCBasCitySizeRateModel.ModifyModel param)
        {
            try
            {
                if (string.IsNullOrEmpty(UserInfo.UserCompanyId))
                {
                    return Error("该用户不属于任何公司!");
                }
                if (string.IsNullOrEmpty(UserInfo.UserExtrSource))
                {
                    return Error("该用户所在公司没有任何数据源!");
                }
                var sqlDel = $@" DELETE  FROM ngoods.ngoods_DC_Bas_CitySizeRate
WHERE   AreaID = '{{0}}'
        AND ModelID = '{{1}}'
        AND SizeTypeFlag = '{{2}}'
        AND ExtrSource = '{UserInfo.UserExtrSource}'
        AND CompanyID = '{UserInfo.UserCompanyId}'; ";
                if (param == null) return Error("参数对象为空");
                if (param.list != null)
                {
                    var sb = new StringBuilder();
                    sb.AppendLine(string.Format(sqlDel, param.list.First().AreaID, param.list.First().ModelID,
                        param.list.First().SizeTypeFlag));
                    var cmd = Helper.GetSqlStringCommand(sb.ToString());
                    Helper.ExecuteNonQuery(cmd);
                    param.list.ToList().ForEach(d =>
                    {
                        d.CompanyID = UserInfo.UserCompanyId;
                        d.ExtrSource = UserInfo.UserExtrSource;
                    });
                    MergeDCBasCitySizeRate(param.list);
                }
                return Success(0);
            }
            catch (Exception ex)
            {
                return Error(ex.Message);
            }
        }

        /// <summary>
        /// 枚举
        /// </summary>
        /// <param name="list"></param>
        /// Writer：梁家健
        /// Create Date：2017-05-15
        public void MergeDCBasCitySizeRate(IList<DCBasCitySizeRateModel.NeceModel> list)
        {
            try
            {
                var uid = Guid.NewGuid().ToString().Replace("-", "");
                var tempTableName = $" ngoods.temp{uid} ";
                const string targetTableName = @" ngoods.ngoods_DC_Bas_CitySizeRate ";
                var dt =
                    DataTableHelper.LinqToDataTable(
                        list.DistinctBy(d => new {d.CompanyID, d.AreaID, d.ModelID, d.SizeID, d.SizeTypeFlag}));
                const string onConditions = @" targetTable.CompanyID=sourceTable.CompanyID
         AND targetTable.ExtrSource=sourceTable.ExtrSource
         AND targetTable.AreaID=sourceTable.AreaID
         AND targetTable.ModelID=sourceTable.ModelID
         AND targetTable.SizeID=sourceTable.SizeID
         AND targetTable.SizeTypeFlag=sourceTable.SizeTypeFlag ";
                const string setConditions = @" SizeRate = sourceTable.SizeRate/100 ";
                const string insertConditions = @" CompanyID ,
                      ExtrSource ,
                      AreaID ,
                      AreaName ,
                      ModelID ,
                      SizeID ,
                      SizeRate ,
                      SizeTypeFlag ";
                const string valuesConditions = @" sourceTable.CompanyID ,
                      sourceTable.ExtrSource ,
                      sourceTable.AreaID ,
                      sourceTable.AreaName ,
                      sourceTable.ModelID ,
                      sourceTable.SizeID ,
                      sourceTable.SizeRate/100 ,
                      sourceTable.SizeTypeFlag ";
                Helper.Merge(tempTableName, targetTableName, dt, onConditions, setConditions, insertConditions,
                    valuesConditions, true);
            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }

        /// Writer：梁家健
        /// Create Date：2017-05-15
        [JsonType]
        [MustLogin]
        [Description("删除城市品类码比")]
        [ReturnType(typeof(int), "0-成功")]
        public ApiResult DeleteDCBasCitySizeRate([ParamDescription("参数")] DCBasCitySizeRateModel.DeleteModel param)
        {
            try
            {
                if (string.IsNullOrEmpty(UserInfo.UserCompanyId))
                {
                    return Error("该用户不属于任何公司!");
                }
                if (string.IsNullOrEmpty(UserInfo.UserExtrSource))
                {
                    return Error("该用户所在公司没有任何数据源!");
                }
                var sql = $@"DELETE  FROM ngoods.ngoods_DC_Bas_CitySizeRate
WHERE   AreaID = '{{0}}'
        AND ModelID = '{{1}}'
        AND ExtrSource = '{UserInfo.UserExtrSource}'
        AND CompanyID = '{UserInfo.UserCompanyId}'; ";
                if (param == null) return Error("参数对象为空");
                if (param.list != null)
                {
                    var sb = new StringBuilder();
                    foreach (var item in param.list)
                    {
                        sb.AppendLine(string.Format(sql, item.AreaID, item.ModelID));
                    }
                    var cmd = Helper.GetSqlStringCommand(sb.ToString());
                    Helper.ExecuteNonQuery(cmd);
                }
                return Success(0);
            }
            catch (Exception ex)
            {
                return Error(ex.Message);
            }
        }

        /// <summary>
        /// 插入数据到临时表
        /// </summary>
        /// <param name="list"></param>
        /// Writer：梁家健
        /// Create Date：2017-05-27
        public void CreateTempTable(DataTable dt, string TempTableName,Dictionary<string,string> Cesd)
        {
            try
            {
                var sql = $@"CREATE TABLE {TempTableName}
(
    CompanyID varchar(30),
    ExtrSource varchar(30),
    AreaCode varchar(60),
    ModelName varchar(120),
    SizeName varchar(120),
    SizeRate numeric(8,4)
)";
                dt.Columns.Remove("AreaName");
                dt.Columns.Add("ExtrSource", System.Type.GetType("System.String"));
                foreach (DataRow item in dt.Rows)
                {
                    var companyID = item["CompanyID"].ToString();
                    item["ExtrSource"] = Cesd.Keys.Contains(companyID) ? Cesd[companyID] : "";
                }
                var cmdListFirst = new List<CommandInfo>
                {
                    new CommandInfo
                    {
                        CommandText = sql,
                    },
                };
                Helper.ExecuteSqlTran(cmdListFirst, dt, TempTableName);
            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }

        /// <summary>
        /// 插入数据到码比表
        /// </summary>
        /// <param name="TempTableName"></param>
        /// Writer：梁家健
        /// Create Date：2017-05-27
        public DataTable ImportDCBasCitySizeRate(string TempTableName, Dictionary<string, string> Cesd)
        {
            try
            {
                var sql = $@"SELECT  table1.CompanyID ,
        table1.ExtrSource ,
        table1.AreaCode ,
        table1.ModelName ,
        table1.SizeName ,
        area.AreaID ,
        area.AreaName ,
        model.ModelID ,
        si.SizeID ,
        table1.SizeRate ,
        mst.SizeTypeFlag
FROM    {TempTableName} table1
        LEFT JOIN ( SELECT  *
                    FROM    nods.nods_erp_Bas_Area
                    WHERE   ExtrSource = '{UserInfo.UserExtrSource}'
                  ) area ON table1.AreaCode = area.AreaCode
                            AND table1.ExtrSource = area.ExtrSource
        LEFT JOIN ( SELECT  *
                    FROM    nods.nods_erp_SD_Mat_Model
                    WHERE   ExtrSource = '{UserInfo.UserExtrSource}'
                  ) model ON table1.ModelName = model.ModelName
                             AND table1.ExtrSource = model.ExtrSource
        LEFT JOIN ( SELECT  ( CASE WHEN SizeName = '2XL' THEN '00000008'
                                   ELSE SizeID
                              END ) AS SizeID ,
                            SizeName ,
                            SizeCodeType ,
                            ExtrSource
                    FROM    nods.nods_erp_SD_Mat_Size
                    WHERE   ExtrSource = '{UserInfo.UserExtrSource}'
                  ) si ON table1.SizeName = si.SizeName
                          AND table1.ExtrSource = si.ExtrSource
        LEFT JOIN ( SELECT  *
                    FROM    ngoods.ngoods_PL_Bas_ModelSizeType
                    WHERE   ExtrSource = '{UserInfo.UserExtrSource}'
                            AND CompanyID = '{UserInfo.UserCompanyId}'
                  ) mst ON model.ModelID = mst.ModelID
                           AND si.SizeCodeType = mst.SizeTypeFlag
                           AND table1.ExtrSource = mst.ExtrSource
                           AND table1.CompanyID = mst.CompanyID";
                var companyID = UserInfo.UserCompanyId;
                var cmd = Helper.GetSqlStringCommand(sql);
                var dt = Helper.ExecuteDataTable(cmd);
                var list =
                    ModelConvertHelper<DCBasCitySizeRateModel.ListModel>.ConvertToModel(dt);
                var resultList =
                    list.Where(
                            d =>
                                d.AreaID != null && d.ModelID != null && d.CompanyID == companyID &&
                                Cesd.Keys.Contains(UserInfo.UserExtrSource))
                        .Select(d => d).GroupBy(d => new {d.CompanyID, d.AreaID, d.ModelID, d.SizeTypeFlag})
                        .Where(d => d.Sum(r => r.SizeRate) >= 0 && d.Sum(r => r.SizeRate) <= 100)
                        .SelectMany(d => d)
                        .ToList();
                resultList.ForEach(d => d.ExtrSource = Cesd[d.CompanyID]);
                MergeDCBasCitySizeRate(resultList.Select(d => new DCBasCitySizeRateModel.NeceModel
                {
                    CompanyID = d.CompanyID,
                    ExtrSource = d.ExtrSource,
                    AreaID = d.AreaID,
                    AreaName = d.AreaName,
                    ModelID = d.ModelID,
                    SizeID = d.SizeID,
                    SizeRate = d.SizeRate,
                    SizeTypeFlag = d.SizeTypeFlag
                }).ToList());
                var errorList = list.Except(resultList).ToList();
                var errorDT = DataTableHelper.LinqToDataTable(errorList);
                //if (errorDT.Rows.Count > 0)
                //{
                //    errorDT.Columns.Remove("AreaID");
                //    errorDT.Columns.Remove("ModelID");
                //    errorDT.Columns.Remove("SizeID");
                //    errorDT.Columns.Remove("SizeTypeFlag");
                //}
                return errorDT;
            }
            catch (Exception ex)
            {
                DropTempTable(TempTableName);
                throw (ex);
            }
        }

        /// <summary>
        /// 删除临时表
        /// </summary>
        /// <param name="TempTableName"></param>
        /// Writer：梁家健
        /// Create Date：2017-05-27
        public void DropTempTable(string TempTableName)
        {
            try
            {
                Helper.DropTempTable(TempTableName);
            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }
    }
}
